package com.lg.startertemplate.dao.jdbc;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.lg.startertemplate.config.DataSourceManager;
import com.lg.startertemplate.exception.CommonException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.function.Function;

/**
 * jdbc封装
 * <p>
 * 数据的操作
 * 曾 删 改 查
 * <p>
 * 表的操作
 * 创建表 删除表  修改表的名称  查询表是否存在
 * <p>
 * 库的操作
 * <p>
 * <p>
 * //创建表
 * create table \"public\".\"" + randomNum + "_data_info\"(\"id\" SERIAL primary key,
 * \"create_time\" timestamp(6),\"update_time\" timestamp(6));
 * <p>
 * //查询表是否存在
 * select count(*) from pg_class where relname = 't_user1';
 * <p>
 * //修改表的名称
 * alter table "course" rename to "course_111"
 * <p>
 * //删除表
 * drop table "t_user"
 * <p>
 * <p>
 * <p>
 * <p>
 * <p>
 * //创建 数据库 名称
 * create database TTS1
 * <p>
 * //修改 数据库 名称
 * alter database TTS rename to tTS1
 * <p>
 * //清除 数据库 链接
 * select pg_terminate_backend(pid) from pg_stat_activity where datname='tts1';
 * <p>
 * //删除 数据库
 * drop database tts1
 * <p>
 * //检测 数据库 是否存在
 * select datname  from pg_catalog.pg_database  where datname='t1s';
 */

@Service
@Slf4j
public class PgDbHelper implements DbHelper {

    @Autowired
    private DataSourceManager dataSourceManager;

    public DruidPooledConnection getConnection(String datasourceName) {
        try {
            return dataSourceManager.getDatasource(datasourceName).getConnection();
        } catch (SQLException e) {
            log.error("获取 " + datasourceName + " 数据源出错", e);
            throw new CommonException("获取数据源出错");
        }
    }

    //------------------------数据操作---------------------
    @Override
    public Integer insert(String datasourceName, String sql) {
        return this.run(datasourceName, sql);
    }

    @Override
    public Integer update(String datasourceName, String sql) {
        return this.run(datasourceName, sql);
    }

    @Override
    public Integer delete(String datasourceName, String sql) {
        return this.run(datasourceName, sql);
    }

    @Override
    public <T> T query(String datasourceName, String sql, Function<ResultSet, T> func) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        T t = null;
        try {
            con = this.getConnection(datasourceName);
            if (null != con) {
                st = con.createStatement();
                if (null != st) {
                    log.debug("执行sql-> " + sql);
                    rs = st.executeQuery(sql);
                    if (null != rs && null != func) {
                        t = func.apply(rs);
                        rs.close();
                    }
                    st.close();
                }
                con.close();
            }
        } catch (Exception ex) {
            log.error("执行sql-> " + sql + " 出错", ex);
            throw new CommonException("执行sql出错");
        } finally {
            try {
                if (null != rs)
                    rs.close();
                if (null != st)
                    st.close();
                if (null != con)
                    con.close();
            } catch (SQLException ex) {
                log.error("关闭数据库连接出错", ex);
                throw new CommonException("执行sql出错");
            }
        }
        return t;
    }

    @Override
    public Long count(String datasourceName, String sql) {
        return this.countForFirst(datasourceName, sql);
    }


    //------------------------表的操作---------------------

    /**
     * 创建表
     */
    @Override
    public Integer createTable(String datasourceName, String sql){
        return this.run(datasourceName, sql);
    }

    /**
     * 查询表是否存在
     */
    @Override
    public Boolean existTable(String datasourceName, String sql) {
        if (this.countForFirst(datasourceName, sql) == 0) {
            return false;
        }

        return true;
    }

    /**
     * 修改 表 名称
     */
    @Override
    public Integer updateTable(String datasourceName, String sql)   {
        return this.run(datasourceName, sql);
    }


    /**
     * 修改 表
     */
    @Override
    public Integer deleteTable(String datasourceName, String sql)   {
        return this.run(datasourceName, sql);
    }


    //------------------------库的操作---------------------
    @Override
    public Integer createDatabase(String datasourceName, String sql)   {
        return this.run(datasourceName, sql);
    }

    @Override
    public Boolean existDatabase(String datasourceName, String sql)   {
        if (this.resultTotal(datasourceName, sql) == 0L)
            return false;
        return true;
    }

    @Override
    public Integer updateDatabase(String datasourceName, String sql)   {
        return this.run(datasourceName, sql);
    }

    @Override
    public Integer deleteDatabase(String datasourceName, String sql)   {
        return this.run(datasourceName, sql);
    }





//	public <T> List<T> getListBySql(String sql, Supplier<T> getObj,
//									Consumer<ResultSet, T> action) {
//		if (getObj == null || action == null)
//			return null;
//
//		List<T> result = pgQuery(sql, rs -> {
//			try {
//				ArrayList<T> list = new ArrayList<T>();
//				while (rs.next()) {
//					T t = getObj.get();
//					action.accept(rs, t);
//					list.add(t);
//				}
//				return list;
//			} catch (Exception e) {
//				e.printStackTrace();
//			}
//			return null;
//		});
//		return result;
//	}

    public Integer run(String datasourceName, String sql)   {
        Connection con = this.getConnection(datasourceName);

        Statement st = null;
        Integer res = 0;

        try {
            if (null != con) {
                st = con.createStatement();
                if (null != st) {
                    log.debug("执行sql-> " + sql);
                    st.execute(sql);
                    st.close();
                }
                con.close();
            }
        } catch (Exception ex) {
            log.error("执行sql-> " + sql + " 出错", ex);
            throw new CommonException("执行sql出错");
        } finally {
            try {
                if (null != st)
                    st.close();
                if (null != con)
                    con.close();
            } catch (SQLException ex) {
                log.error("关闭数据库连接出错", ex);
                throw new CommonException("执行sql出错");
            }
        }
        return res;

    }

    //执行count 语句,返回 返回值中第一行的count值
    public Long countForFirst(String datasourceName, String sql)   {
        Connection con = this.getConnection(datasourceName);
        Statement st = null;
        ResultSet rs = null;
        Long count = null;
        try {
            if (null != con) {
                st = con.createStatement();
                if (null != st) {
                    log.debug("执行sql-> " + sql);
                    rs = st.executeQuery(sql);
                    if (null != rs) {
                        while (rs.next()) {
                            return rs.getLong("count");
                        }
                    }
                    st.close();
                }
                con.close();
            }
        } catch (Exception ex) {
            log.error("执行sql-> " + sql + " 出错", ex);
            throw new CommonException("执行sql出错");
        } finally {
            try {
                if (null != rs)
                    rs.close();
                if (null != st)
                    st.close();
                if (null != con)
                    con.close();
            } catch (SQLException ex) {
                log.error(ex.getMessage(), ex);
            }
        }
        return count;
    }


    public Long resultTotal(String datasourceName, String sql)   {
        Connection con = this.getConnection(datasourceName);
        Statement st = null;
        ResultSet rs = null;
        Long totalNum = 0L;
        try {
            if (null != con) {
                st = con.createStatement();
                if (null != st) {
                    log.debug("执行sql-> " + sql);
                    rs = st.executeQuery(sql);
                    if (null != rs) {
                        while (rs.next()) {
                            totalNum++;
                        }
                    }
                    st.close();
                }
                con.close();
            }
        } catch (Exception ex) {
            log.error("执行sql-> " + sql + " 出错", ex);
            throw new CommonException("执行sql出错");
        } finally {
            try {
                if (null != rs)
                    rs.close();
                if (null != st)
                    st.close();
                if (null != con)
                    con.close();
            } catch (SQLException ex) {
                log.error(ex.getMessage(), ex);
            }
        }
        return totalNum;
    }





}
